package com.egame.manage.biz.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import cn.egame.common.util.Utils;
import cn.egame.interfaces.EnumType.GameStatus;
import cn.egame.interfaces.EnumType.FeeType;
import cn.egame.interfaces.EnumType.PayType;
import cn.egame.interfaces.cp.ContentProviderInfo;
import cn.egame.interfaces.gc.GameInfo;
import cn.egame.interfaces.pu.AppParameter;
import com.egame.manage.biz.server.ManageClient;
import com.egame.manage.biz.server.gc.GameServiceImpl;
import com.egame.manage.biz.server.pu.ParameterServiceImpl;
import com.egame.manage.biz.utils.ConstVar;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class ExcelUtils {
	public WritableWorkbook getExcel(File file) throws IOException{
		WritableWorkbook book = Workbook.createWorkbook(file);
		
		return book;
	}
	
	public static Map<Integer, String> ImportExcel(InputStream is) throws BiffException {
		
		Map<Integer, String> data = new HashMap<Integer, String>();
		try {
			Workbook book = Workbook.getWorkbook(is);
			if(null == book) {
				return null;
			} 
			Sheet sheet = book.getSheet(0);
			int rows = sheet.getRows();
			int cols = sheet.getColumns();
			if(cols == 3){
				for(int i=1; i<rows; i++) {
					String key = sheet.getCell(0,i).getContents();					
					String value = sheet.getCell(2,i).getContents();
					if(!Utils.stringIsNullOrEmpty(key) && !Utils.stringIsNullOrEmpty(value)){
						data.put(Integer.valueOf(key), value);
					}				
				}
			}			
			book.close();
		} catch(IOException e) {
			e.printStackTrace();
		}
		return data;
	}
		
	public static void ExportExcel(List<Map<Integer,Object>> lists,String filePath){
		// 准备设置excel工作表的标题     
        String[] title = {"游戏编号","游戏名称","游戏标签"};     
        try {     
            // 创建Excel工作薄     
            WritableWorkbook wwb;     
            OutputStream os = new FileOutputStream(filePath);     
            wwb=Workbook.createWorkbook(os);      
            // 添加第一个工作表并设置第一个Sheet的名字     
            WritableSheet sheet = wwb.createSheet("游戏标签导出", 0);     
            Label label = null;     
            for(int i=0;i<title.length;i++){     
                label = new Label(i,0,title[i]);     
                sheet.addCell(label);     
            }     
            //填充游戏编码，游戏名称，游戏标签
            int length = lists.size();
            for(int i=0;i<length;i++) {
            	Map<Integer,Object> map = new HashMap<Integer,Object>();
            	@SuppressWarnings("rawtypes")
            	Iterator ite = map.entrySet().iterator();
            	while(ite.hasNext()) {
    				@SuppressWarnings("rawtypes")
					Map.Entry entry = (Map.Entry)ite.next();
    				//游戏编码
    				label = new Label(0,i+1,entry.getKey().toString());
    				sheet.addCell(label);
    				String nameLabel = entry.getValue().toString();
    				String[] name = nameLabel.split("/");
    				//游戏名称
    				label = new Label(1,i+1,name[0]);
    				sheet.addCell(label);
    				//游戏标签
    				label = new Label(2,i+1,name[1]);
    				sheet.addCell(label);
    			}
            }
            // 写入数据     
            wwb.write();     
            // 关闭文件     
            wwb.close();     
        } catch (Exception e) {     
            e.printStackTrace();     
        }     
	}
	
	public static WritableWorkbook ExportGameInfo(OutputStream os,GameStatus status)throws Exception{
		// 准备设置excel工作表的标题     
        String[] title = {"游戏编号","更新时间","产品名称","CP名称","产品分类","产品类型","产品子类","是否入包","计费类型","支付类型","游戏介绍"};    
        WritableWorkbook wwb = null;
        try {     
            wwb=Workbook.createWorkbook(os);      
            WritableSheet sheet = wwb.createSheet("游戏数据导出", 0);     
            Label label = null;     
            for(int i=0;i<title.length;i++){     
                label = new Label(i,0,title[i]);     
                sheet.addCell(label);     
            }
            
            GameInfo bean = new GameInfo();
            bean.setGameStatus(status);
            List<Integer> ids = GameServiceImpl.getInstance().listGameIdList(bean);
            ids = GameServiceImpl.getInstance().listGameIdsByGameStatus(ids, status);
            List<GameInfo> list = GameServiceImpl.getInstance().listGameInfoByIds(0, 0L, ids);
            for(int i=0;i<list.size();i++){
            	//游戏编号
            	sheet.addCell(new Label(0, i+1, String.valueOf(list.get(i).getGId())));
            	//更新时间
            	sheet.addCell(new Label(1, i+1, list.get(i).getUpdateTime().toLocaleString()));
            	//产品名称
            	sheet.addCell(new Label(2, i+1, list.get(i).getGameName()));
            	//CP名称
            	ContentProviderInfo cp = ManageClient.getInstance().getContentProviderInfoById(0, 0L, list.get(i).getCpId());
            	sheet.addCell(new Label(3, i+1, cp==null?"":cp.getCnName()));
            	//产品分类
            	AppParameter productApp = GameServiceImpl.getInstance().getAppParameterByGId(0, 0L, list.get(i).getGId());
            	sheet.addCell(new Label(4, i+1, productApp==null?"":productApp.getName()));
            	//产品类型
            	sheet.addCell(new Label(5, i+1, list.get(i).getNetworkType()==null?"":list.get(i).getNetworkType().getMessage()));
            	//产品子类
            	String subtype = ParameterServiceImpl.getInstance().getParameterNameById(0,0L,ConstVar.APP_OS,""+list.get(i).getOsCode());
            	sheet.addCell(new Label(6, i+1, subtype));
            	//是否入包
            	sheet.addCell(new Label(7, i+1, list.get(i).getIsInpackage()>0?"是":"否"));
            	//计费类型
            	sheet.addCell(new Label(8, i+1, FeeType.lookup(list.get(i).getFeeCode())==null?"":FeeType.lookup(list.get(i).getFeeCode()).getMessage()));
                //支付类型
            	sheet.addCell(new Label(9, i+1, PayType.lookup(list.get(i).getPayCode())==null?"":PayType.lookup(list.get(i).getPayCode()).getMessage()));
                //游戏介绍
            	sheet.addCell(new Label(10, i+1, list.get(i).getIntroduction()));
            }
            
            // 写入数据     
             wwb.write();  
                 
        } catch (Exception e) {     
            e.printStackTrace();     
        }finally{
        	// 关闭文件     
            wwb.close();
        }        
        return null;      
	}

	public static WritableWorkbook ExportDataExcel(List<String[]> list,OutputStream os) throws IOException, WriteException{
		// 准备设置excel工作表的标题     
        String[] title = {"游戏编号","游戏名称","游戏标签"};    
        WritableWorkbook wwb = null;
        try {     
            wwb=Workbook.createWorkbook(os);      
            WritableSheet sheet = wwb.createSheet("游戏标签导出", 0);     
            Label label = null;     
            for(int i=0;i<title.length;i++){     
                label = new Label(i,0,title[i]);     
                sheet.addCell(label);     
            } 
            int i=0;
            for(String[] game:list){
            	if(game != null && game.length==3){
            		sheet.addCell(new Label(0,i+1,game[0]));
            		sheet.addCell(new Label(1,i+1,game[1]));
            		sheet.addCell(new Label(2,i+1,game[2]));
            	}
            	i++;
            }            
            // 写入数据     
             wwb.write();  
                 
        } catch (Exception e) {     
            e.printStackTrace();     
        }finally{
        	// 关闭文件     
            wwb.close();
        }        
        return null;        
	}
	
}
